CREATE TABLE [dbo].[Name_FR]
(
[ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_FR_ID] DEFAULT (''),
[SOLICITOR_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_FR_SOLICITOR_ID] DEFAULT (''),
[DO_NOT_PHONE] [bit] NOT NULL CONSTRAINT [DF_Name_FR_DO_NOT_PHONE] DEFAULT ((0)),
[DO_NOT_EMAIL] [bit] NOT NULL CONSTRAINT [DF_Name_FR_DO_NOT_EMAIL] DEFAULT ((0)),
[LIST_AS] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_FR_LIST_AS] DEFAULT (''),
[MATCH_DESCRIP] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_FR_MATCH_DESCRIP] DEFAULT (''),
[MATCH_PCT] [tinyint] NOT NULL CONSTRAINT [DF_Name_FR_MATCH_PCT] DEFAULT ((0)),
[MIN_EMPL_CONTRIB] [money] NOT NULL CONSTRAINT [DF_Name_FR_MIN_EMPL_CONTRIB] DEFAULT ((0)),
[MAX_EMPL_CONTRIB] [numeric] (15, 2) NOT NULL CONSTRAINT [DF_Name_FR_MAX_EMPL_CONTRIB] DEFAULT ((0)),
[VALID_FROM] [datetime] NULL,
[VALID_THRU] [datetime] NULL,
[PREFERRED_DIST_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_FR_PREFERRED_DIST_CODE] DEFAULT (''),
[Receipt_Interval] [tinyint] NOT NULL CONSTRAINT [DF_Name_FR_Receipt_Interval] DEFAULT ((0)),
[Last_Receipt_Printed_Date] [datetime] NULL,
[DO_NOT_SELL] [bit] NOT NULL CONSTRAINT [DF_Name_FR_DO_NOT_SELL] DEFAULT ((0)),
[DO_NOT_SOLICIT] [bit] NOT NULL CONSTRAINT [DF_Name_FR_DO_NOT_SOLICIT] DEFAULT ((0)),
[TIME_STAMP] [timestamp] NULL
) ON [PRIMARY]

GO

CREATE TRIGGER asi_Name_FR_Insert_Update_Delete ON Name_FR
AFTER UPDATE, INSERT, DELETE
AS
    DECLARE @UpdateType VARCHAR(1)
    DECLARE @InsertFlag BIT
    DECLARE @DeleteFlag BIT

    IF (TRIGGER_NESTLEVEL( object_ID('asi_Name_Salutation_Insert_Update_Delete')) > 0)
        RETURN;
            
    -- check what we are doing
    
    SET @UpdateType='X';
    SET @InsertFlag = 0;
    SET @DeleteFlag = 0;
    
    IF EXISTS (SELECT 1 FROM INSERTED)
        SET @InsertFlag = 1;
        
    IF EXISTS (SELECT 1 FROM DELETED)
        SET @DeleteFlag = 1;
        
    IF @InsertFlag=1 AND @DeleteFlag=0
        SET @UpdateType = 'I'; -- INSERTING
        
    IF @InsertFlag=1 AND @DeleteFlag=1
        SET @UpdateType = 'U'; -- UPDATING
        
    IF @InsertFlag=0 AND @DeleteFlag=1
        SET @UpdateType = 'D' -- DELETING
        
    IF @InsertFlag=0 AND @DeleteFlag=0
        RETURN; -- DO NOTHING
    
    IF @UpdateType='U'
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM inserted i INNER JOIN deleted d ON i.ID = d.ID WHERE i.LIST_AS <> d.LIST_AS)
        BEGIN
            -- There are no actual updates to LIST_AS, so quit out
            RETURN;
        END
    END;
    
    SET NOCOUNT ON
    
    -- if inserting or updating
    IF @UpdateType='I' OR @UpdateType='U'        
    BEGIN    
        UPDATE [dbo].[Name_Salutation]
            SET [Name_Salutation].[SALUTATION_TEXT]=i.[LIST_AS]
            FROM [INSERTED] i
            WHERE i.[ID]=[Name_Salutation].[ID]
            AND [Name_Salutation].[SALUTATION_TYPE]='LIST_AS'
            AND [Name_Salutation].[SALUTATION_TEXT]<>i.[LIST_AS];

        INSERT INTO [dbo].[Name_Salutation]
            ( [ID] ,
              [SALUTATION_TYPE] ,
              [SALUTATION_TEXT]
            )
            SELECT i.[ID] , -- ID - varchar(10)
            'LIST_AS' , -- SALUTATION_TYPE - varchar(60)
            i.[LIST_AS] -- SALUTATION_TEXT - varchar(255)
            FROM [INSERTED] i
            WHERE NOT EXISTS (SELECT 1 FROM [dbo].[Name_Salutation] n
                    WHERE n.[ID]=i.[ID]
                      AND n.[SALUTATION_TYPE]='LIST_AS');
                      
            
    END;
    ELSE IF @UpdateType='D'
    BEGIN
        -- the Name_FR row has been deleted, delete the Name_Salutation row as well
        DELETE FROM [dbo].[Name_Salutation]
            WHERE [Name_Salutation].[SALUTATION_TYPE]='LIST_AS'
            AND EXISTS (SELECT 1 FROM [DELETED] d WHERE d.[ID]=[Name_Salutation].[ID]);
    END;
    

GO
CREATE NONCLUSTERED INDEX [iName_FRID] ON [dbo].[Name_FR] ([ID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iName_FRLast_Receipt_Date] ON [dbo].[Name_FR] ([Last_Receipt_Printed_Date]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iName_FRBatch_Receipt_Interval] ON [dbo].[Name_FR] ([Receipt_Interval]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iName_FRSOLICITOR_ID] ON [dbo].[Name_FR] ([SOLICITOR_ID]) ON [PRIMARY]
GO
GRANT REFERENCES ON  [dbo].[Name_FR] TO [IMIS]
GRANT SELECT ON  [dbo].[Name_FR] TO [IMIS]
GRANT INSERT ON  [dbo].[Name_FR] TO [IMIS]
GRANT DELETE ON  [dbo].[Name_FR] TO [IMIS]
GRANT UPDATE ON  [dbo].[Name_FR] TO [IMIS]
GO